use rusqlite::{Connection, Result, params};
use std::fs;
use std::path::Path;
use serde::{Serialize, Deserialize};
use std::sync::{Arc, Mutex};
use log::{info, debug};
use std::collections::HashMap;

#[derive(Debug, Serialize, Deserialize)]
pub struct Riddle {
    pub id: i64,
    pub question: String,
    pub answer: String,
    pub type_: String,
    pub options: Option<String>,
    pub difficulty: i64,
    pub used: bool,
    pub game_id: Option<i64>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Game {
    pub id: i64,
    pub name: String,
    pub description: Option<String>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Setting {
    pub key: String,
    pub value: String,
}

pub struct DbManager {
    conn: Arc<Mutex<Connection>>,
}

impl DbManager {
    pub fn new(db_path: &str) -> Result<Self> {
        // 确保目录存在
        if let Some(parent) = Path::new(db_path).parent() {
            fs::create_dir_all(parent).map_err(|e| rusqlite::Error::SqliteFailure(
                rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_CANTOPEN),
                Some(format!("Failed to create directory: {}", e))
            ))?;
        }

        // 创建或打开数据库连接
        let conn = Connection::open(db_path)?;
        
        // 读取初始化SQL脚本
        let init_sql = include_str!("init.sql");
        
        // 执行初始化脚本
        conn.execute_batch(init_sql)?;
        
        info!("Database initialized successfully");
        
        Ok(Self {
            conn: Arc::new(Mutex::new(conn)),
        })
    }
    
    // 获取所有游戏
    pub fn get_games(&self) -> Result<Vec<Game>> {
        let conn = self.conn.lock().unwrap();
        let mut stmt = conn.prepare("SELECT id, name, description FROM games")?;
        let game_rows = stmt.query_map([], |row| {
            Ok(Game {
                id: row.get(0)?,
                name: row.get(1)?,
                description: row.get(2)?,
            })
        })?;
        
        let mut games = Vec::new();
        for game in game_rows {
            games.push(game?);
        }
        
        Ok(games)
    }
    
    // 获取未使用的谜语（从通用表）
    pub fn get_unused_riddles(&self, count: usize, type_: Option<&str>) -> Result<Vec<Riddle>> {
        let conn = self.conn.lock().unwrap();
        
        let mut result = Vec::new();
        
        if let Some(t) = type_ {
            let mut stmt = conn.prepare("SELECT id, question, answer, type, options, difficulty, used, game_id FROM riddles WHERE used = 0 AND type = ? ORDER BY RANDOM() LIMIT ?")?;
            let riddle_rows = stmt.query_map([t, &count.to_string()], |row| {
                Ok(Riddle {
                    id: row.get(0)?,
                    question: row.get(1)?,
                    answer: row.get(2)?,
                    type_: row.get(3)?,
                    options: row.get(4)?,
                    difficulty: row.get(5)?,
                    used: row.get::<_, i64>(6)? != 0,
                    game_id: row.get(7)?,
                })
            })?;
            
            for riddle in riddle_rows {
                result.push(riddle?);
            }
        } else {
            let mut stmt = conn.prepare("SELECT id, question, answer, type, options, difficulty, used, game_id FROM riddles WHERE used = 0 ORDER BY RANDOM() LIMIT ?")?;
            let riddle_rows = stmt.query_map([&count.to_string()], |row| {
                Ok(Riddle {
                    id: row.get(0)?,
                    question: row.get(1)?,
                    answer: row.get(2)?,
                    type_: row.get(3)?,
                    options: row.get(4)?,
                    difficulty: row.get(5)?,
                    used: row.get::<_, i64>(6)? != 0,
                    game_id: row.get(7)?,
                })
            })?;
            
            for riddle in riddle_rows {
                result.push(riddle?);
            }
        }
        
        Ok(result)
    }
    
    // 获取特定游戏的未使用谜语
    pub fn get_unused_riddles_by_game(&self, game_id: i64, count: usize) -> Result<Vec<Riddle>> {
        let conn = self.conn.lock().unwrap();
        let mut result = Vec::new();
        
        // 根据游戏ID选择不同的表
        match game_id {
            1 => {
                // 猜谜语游戏（选择题）
                debug!("Getting riddles from riddle_game table");
                let mut stmt = conn.prepare("SELECT id, question, answer, options, difficulty, used FROM riddle_game WHERE used = 0 ORDER BY RANDOM() LIMIT ?")?;
                let riddle_rows = stmt.query_map([&count.to_string()], |row| {
                    Ok(Riddle {
                        id: row.get(0)?,
                        question: row.get(1)?,
                        answer: row.get(2)?,
                        type_: "choice".to_string(),
                        options: row.get(3)?,
                        difficulty: row.get(4)?,
                        used: row.get::<_, i64>(5)? != 0,
                        game_id: Some(1),
                    })
                })?;
                
                for riddle in riddle_rows {
                    result.push(riddle?);
                }
            },
            2 => {
                // 脑筋急转弯游戏（问答题）
                debug!("Getting riddles from brain_teaser_game table");
                let mut stmt = conn.prepare("SELECT id, question, answer, difficulty, used FROM brain_teaser_game WHERE used = 0 ORDER BY RANDOM() LIMIT ?")?;
                let riddle_rows = stmt.query_map([&count.to_string()], |row| {
                    Ok(Riddle {
                        id: row.get(0)?,
                        question: row.get(1)?,
                        answer: row.get(2)?,
                        type_: "qa".to_string(),
                        options: None,
                        difficulty: row.get(3)?,
                        used: row.get::<_, i64>(4)? != 0,
                        game_id: Some(2),
                    })
                })?;
                
                for riddle in riddle_rows {
                    result.push(riddle?);
                }
            },
            _ => {
                // 默认使用通用表
                debug!("Getting riddles from riddles table for game_id: {}", game_id);
                let mut stmt = conn.prepare("SELECT id, question, answer, type, options, difficulty, used FROM riddles WHERE used = 0 AND game_id = ? ORDER BY RANDOM() LIMIT ?")?;
                let riddle_rows = stmt.query_map([&game_id.to_string(), &count.to_string()], |row| {
                    Ok(Riddle {
                        id: row.get(0)?,
                        question: row.get(1)?,
                        answer: row.get(2)?,
                        type_: row.get(3)?,
                        options: row.get(4)?,
                        difficulty: row.get(5)?,
                        used: row.get::<_, i64>(6)? != 0,
                        game_id: Some(game_id),
                    })
                })?;
                
                for riddle in riddle_rows {
                    result.push(riddle?);
                }
            }
        }
        
        Ok(result)
    }
    
    // 标记谜语为已使用
    pub fn mark_riddle_as_used(&self, id: i64, game_id: Option<i64>) -> Result<()> {
        let conn = self.conn.lock().unwrap();
        
        // 更新通用表
        conn.execute("UPDATE riddles SET used = 1 WHERE id = ?", [id])?;
        
        // 如果提供了游戏ID，也更新对应的游戏表
        if let Some(gid) = game_id {
            match gid {
                1 => {
                    conn.execute("UPDATE riddle_game SET used = 1 WHERE id = ?", [id])?;
                },
                2 => {
                    conn.execute("UPDATE brain_teaser_game SET used = 1 WHERE id = ?", [id])?;
                },
                _ => {}
            }
        }
        
        Ok(())
    }
    
    // 重置所有谜语的使用状态
    pub fn reset_riddles(&self) -> Result<()> {
        let conn = self.conn.lock().unwrap();
        
        // 重置所有表的使用状态
        conn.execute("UPDATE riddles SET used = 0", [])?;
        conn.execute("UPDATE riddle_game SET used = 0", [])?;
        conn.execute("UPDATE brain_teaser_game SET used = 0", [])?;
        
        Ok(())
    }
    
    // 重置特定游戏的谜语使用状态
    pub fn reset_riddles_by_game(&self, game_id: i64) -> Result<()> {
        let conn = self.conn.lock().unwrap();
        
        match game_id {
            1 => {
                conn.execute("UPDATE riddle_game SET used = 0", [])?;
            },
            2 => {
                conn.execute("UPDATE brain_teaser_game SET used = 0", [])?;
            },
            _ => {
                conn.execute("UPDATE riddles SET used = 0 WHERE game_id = ?", [game_id])?;
            }
        }
        
        Ok(())
    }

    /// 获取单个设置值
    pub fn get_setting(&self, key: &str) -> Result<Option<String>> {
        let conn = self.conn.lock().unwrap();
        let mut stmt = conn.prepare("SELECT value FROM settings WHERE key = ?")?;
        let mut rows = stmt.query_map(params![key], |row| row.get(0))?;
        
        if let Some(value) = rows.next() {
            Ok(Some(value?))
        } else {
            Ok(None)
        }
    }

    /// 获取所有设置
    pub fn get_all_settings(&self) -> Result<HashMap<String, String>> {
        let conn = self.conn.lock().unwrap();
        let mut stmt = conn.prepare("SELECT key, value FROM settings")?;
        let rows = stmt.query_map(params![], |row| {
            Ok((row.get(0)?, row.get(1)?))
        })?;

        let mut settings = HashMap::new();
        for row in rows {
            let (key, value): (String, String) = row?;
            settings.insert(key, value);
        }

        Ok(settings)
    }

    /// 更新单个设置
    pub fn update_setting(&mut self, key: &str, value: &str) -> Result<()> {
        let conn = self.conn.lock().unwrap();
        conn.execute(
            "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
            params![key, value],
        )?;
        Ok(())
    }

    /// 批量更新设置
    pub fn update_settings(&mut self, settings: &HashMap<String, String>) -> Result<()> {
        let mut conn = self.conn.lock().unwrap();
        let tx = conn.transaction()?;
        
        for (key, value) in settings {
            tx.execute(
                "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
                params![key, value],
            )?;
        }

        tx.commit()?;
        Ok(())
    }
}